/**
 * DatabaseConnector.java
 * This class handles the connection and every query with the database.
 * 
 */
package com.flopbox.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

public class DatabaseConnector {

    private static String dbURL = "jdbc:derby://localhost:1527/flopBox;create=false;user=floppy;password=floppy";
    private static String tableName = "fileinfo";
    private static Connection con = null;

    /**
     * Public constructor.
     * This constructor sets the driver for the DB connection.
     *
     */
    public DatabaseConnector() {
        try {
            Class.forName("org.apache.derby.jdbc.ClientDriver");
            //Get a connection
            con = DriverManager.getConnection(dbURL);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * Insert file info into DB.
     * This method inserts the following data to the database:
     * * Date, the modified date for the file
     * * String, the filename of the file
     * * Boolean, true if the file is deleted
     *
     * @param f, FileInfo
     * @see com.flopbox.server.FileInfo
     * @return true if the statement executed successfully, false otherwise
     */
    public boolean insertFileInfo(FileInfo f) {
        String query = "INSERT INTO " + tableName + "(modified, filename, isDeleted) VALUES (?, ?, ?)";

        try (PreparedStatement stmt = con.prepareStatement(query);) {
            stmt.setDate(1, new java.sql.Date(f.getModified().getTime()));
            stmt.setString(2, f.getFilename());
            stmt.setBoolean(3, f.isDeleted());
            
            // Return true if a field was updated
            return stmt.executeUpdate() > 0;

        } catch (SQLException sqlExcept) {
            sqlExcept.printStackTrace();
            return false;
        }
    }
    /**
     * Update the file info.
     * This method updates the file info of a file.
     * It updates the following tables:
     * * Date, the modified date for the file
     * * String, the filename of the file
     * * Boolean, true if the file is deleted
     *
     * @param f, FileInfo
     * @see com.flopbox.server.FileInfo
     * @return true if the statement executed successfully, false otherwise
     */
    public boolean updateFileInfo(FileInfo f) {
        String query = "UPDATE " + tableName + " SET modified=? AND isDeleted=? WHERE filename=?";

        try (PreparedStatement stmt = con.prepareStatement(query);) {
            stmt.setDate(1, new java.sql.Date(f.getModified().getTime()));
            stmt.setBoolean(2, f.isDeleted());
            stmt.setString(3, f.getFilename());
            
            // Return true if a field was updated
            return stmt.executeUpdate() > 0;

        } catch (SQLException sqlExcept) {
            sqlExcept.printStackTrace();
            return false;
        }
    }

    /**
     * Method to get info about all the files stored in the database.
     * This method return an array with information about all the files
     * which are stored in the database.
     *
     * @return ArrayList of FileInfo for all the files stored in the database
     * @see java.util.ArrayList
     * @see com.flopbox.server.FileInfo
     * @throws SQLException if the query doesnt succeed
     */
    public ArrayList<FileInfo> getAllFiles() {
        ArrayList<FileInfo> arr = new ArrayList<>();

        String query = "SELECT * FROM " + tableName;

        try (Statement stmt = con.createStatement()) {
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                Date modified = new java.util.Date(rs.getDate("modified").getTime());
                String filename = rs.getString("filename");
                boolean deleted = rs.getBoolean("isDeleted");
                arr.add(new FileInfo(modified, filename, deleted));
            }


        } catch (SQLException sqlExcept) {
            sqlExcept.printStackTrace();
            return null;
        }

        return arr;
    }

    /**
     * A private method to shutdown the connection to database.
     * This method initiate a shutdown of the connection to the database.
     */
    private static void shutdown() {
        try {
            if (con != null) {
                DriverManager.getConnection(dbURL + ";shutdown=true");
                con.close();
            }
        } catch (SQLException sqlExcept) {
        }

    }

    /**
     * Main method.
     * This method is for testing purposes only.
     */
    public static void main(String[] args) {

        DatabaseConnector db = new DatabaseConnector();
        ArrayList<FileInfo> list = db.getAllFiles();
        for (FileInfo f : list) {
            System.out.println(f.getFilename());
        }

    }
}